﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using NPOI.XSSF.UserModel;

namespace WPay.Infrastructure.Unity
{
    public static class NPOIHelper
    {
        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string[] columnNames)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            using (MemoryStream ms = Export(dtSource, strHeaderText, columnNames))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="columnNames">列名</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] columnNames)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            if (columnNames != null && columnNames.Length != dtSource.Columns.Count)
            {
                throw new ArgumentException("参数不正确:columnNames,数组元素的个数需要和数据源列的数量相同!");
            }

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();

            #region 右击文件 属性信息
            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "上海驰亚防伪科技有限公司";
            //    workbook.DocumentSummaryInformation = dsi;

            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    si.Author = "技术部";
            //    si.Title = strHeaderText;
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}
            #endregion
            //日期格式
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");

            //文本格式
            IDataFormat dataformat = workbook.CreateDataFormat();
            ICellStyle txtstle = workbook.CreateCellStyle();
            txtstle.DataFormat = dataformat.GetFormat("text");
            txtstle.WrapText = true;//设置换行这个要先设置
            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    //{
                    //    IRow headerRow = sheet.CreateRow(0);
                    //    headerRow.HeightInPoints = 25;
                    //    //headerRow.CreateCell(0).SetCellValue(strHeaderText);

                    //    ICellStyle headStyle = workbook.CreateCellStyle();
                    //    headStyle.Alignment = HorizontalAlignment.Center;
                    //    IFont font = workbook.CreateFont();
                    //    font.FontHeightInPoints = 20;
                    //    font.Boldweight = 700;
                    //    headStyle.SetFont(font);
                    //    //headerRow.GetCell(0).CellStyle = headStyle;
                    //    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    //}
                    #endregion


                    #region 列头及样式
                    {

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 100;
                        IRow headerRow = sheet.CreateRow(0);
                        headStyle.SetFont(font);
                        if (columnNames == null)
                        {
                            foreach (DataColumn column in dtSource.Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                //设置列宽
                                var datalenth = arrColWidth[column.Ordinal] + 1;
                                if (datalenth >= 50)//大于50自动换行
                                {
                                    datalenth = 50;
                                }
                                sheet.SetColumnWidth(column.Ordinal, datalenth * 256);
                            }
                        }
                        else
                        {
                            foreach (DataColumn column in dtSource.Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                //设置列宽
                                var datalenth = arrColWidth[column.Ordinal] + 1;
                                if (datalenth >= 50)//大于50自动换行
                                {
                                    datalenth = 50;
                                }
                                sheet.SetColumnWidth(column.Ordinal, datalenth * 256);
                            }
                        }


                    }
                    #endregion

                    rowIndex = 1;
                }
                #endregion


                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = txtstle;
                            break;
                        case "System.DateTime"://日期类型
                            if (!string.IsNullOrEmpty(drValue))//处理空值日期
                            {
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle;
                            }
                            else
                            {
                                newCell.SetCellValue("");
                            }

                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = txtstle;//格式化显示
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }

            //保存文件
            using (FileStream fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "/workbook.xls", FileMode.OpenOrCreate, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                //ms.Position = 0;
                //sheet.Dispose();
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                return ms;
            }
        }

        public static MemoryStream Export(Dictionary<string, DataTable> dtSourcelist, string strHeaderText, string[] columnNames, string filepath)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            IWorkbook workbook = new XSSFWorkbook();
            dtSourcelist.ToList().ForEach(x =>
            {
                var dtSource = x.Value;
                ISheet sheet = workbook.CreateSheet(x.Key);

                #region 右击文件 属性信息
                //{
                //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                //    dsi.Company = "上海驰亚防伪科技有限公司";
                //    workbook.DocumentSummaryInformation = dsi;

                //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //    si.Author = "技术部";
                //    si.Title = strHeaderText;
                //    si.CreateDateTime = DateTime.Now;
                //    workbook.SummaryInformation = si;
                //}
                #endregion
                //日期格式
                ICellStyle dateStyle = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");

                //文本格式
                IDataFormat dataformat = workbook.CreateDataFormat();
                ICellStyle txtstle = workbook.CreateCellStyle();
                txtstle.DataFormat = dataformat.GetFormat("text");
                txtstle.WrapText = true;//设置换行这个要先设置
                                        //取得列宽
                int[] arrColWidth = new int[dtSource.Columns.Count];
                foreach (DataColumn item in dtSource.Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                int rowIndex = 0;
                foreach (DataRow row in dtSource.Rows)
                {
                    #region 新建表，填充表头，填充列头，样式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }

                        #region 表头及样式
                        //{
                        //    IRow headerRow = sheet.CreateRow(0);
                        //    headerRow.HeightInPoints = 25;
                        //    //headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        //    ICellStyle headStyle = workbook.CreateCellStyle();
                        //    headStyle.Alignment = HorizontalAlignment.Center;
                        //    IFont font = workbook.CreateFont();
                        //    font.FontHeightInPoints = 20;
                        //    font.Boldweight = 700;
                        //    headStyle.SetFont(font);
                        //    //headerRow.GetCell(0).CellStyle = headStyle;
                        //    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //}
                        #endregion


                        #region 列头及样式
                        {

                            ICellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            IFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.Boldweight = 100;
                            IRow headerRow = sheet.CreateRow(0);
                            headStyle.SetFont(font);
                            if (columnNames == null)
                            {
                                foreach (DataColumn column in dtSource.Columns)
                                {
                                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                    //设置列宽
                                    var datalenth = arrColWidth[column.Ordinal] + 1;
                                    if (datalenth >= 50)//大于50自动换行
                                    {
                                        datalenth = 50;
                                    }
                                    sheet.SetColumnWidth(column.Ordinal, datalenth * 256);
                                }
                            }
                            else
                            {
                                foreach (DataColumn column in dtSource.Columns)
                                {
                                    headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]);
                                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                    //设置列宽
                                    var datalenth = arrColWidth[column.Ordinal] + 1;
                                    if (datalenth >= 50)//大于50自动换行
                                    {
                                        datalenth = 50;
                                    }
                                    sheet.SetColumnWidth(column.Ordinal, datalenth * 256);
                                }
                            }


                        }
                        #endregion

                        rowIndex = 1;
                    }
                    #endregion


                    #region 填充内容
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        ICell newCell = dataRow.CreateCell(column.Ordinal);

                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                newCell.CellStyle = txtstle;
                                break;
                            case "System.DateTime"://日期类型
                                if (!string.IsNullOrEmpty(drValue))//处理空值日期
                                {
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    newCell.SetCellValue(dateV);
                                    newCell.CellStyle = dateStyle;
                                }
                                else
                                {
                                    newCell.SetCellValue("");
                                }

                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                newCell.CellStyle = txtstle;//格式化显示
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }

                    }
                    #endregion

                    rowIndex++;
                }
            });

            //保存文件
            using (FileStream fs = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                //ms.Position = 0;
                //sheet.Dispose();
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                return ms;
            }
        }
        /// <summary>
        /// 获取csv的内存流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="array">数据集合</param>
        /// <param name="exceptColumns">排除的字段</param>
        /// <param name="IsAddHeader">是否添加表头(1-是，0-否,默认1)</param>
        /// <returns></returns>
        public static MemoryStream GetStream<T>(IEnumerable<T> array, string exceptColumns, int IsAddHeader = 1)
        {
            StringBuilder sb = new StringBuilder();
            if (IsAddHeader == 1)
            {
                int i = 0;
                //添加表头
                foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
                {
                    if (!exceptColumns.Contains("," + dp.Name + ","))
                    {
                        if (i > 0)
                        {
                            sb.Append(",");
                        }
                        //获取字段的描述
                        // sb.Append("\"").Append("\t").Append((dp.Description ?? "").Replace("\"", "\"\"")).Append("\"");
                        //获取字段的名称
                        sb.Append("\"").Append("\t").Append((dp.DisplayName ?? "").Replace("\"", "\"\"")).Append("\"");
                        i++;
                    }
                }
                sb.Append("\n");
            }
            //添加行数据
            foreach (T item in array)
            {
                int j = 0;
                foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
                {
                    if (!exceptColumns.Contains("," + dp.Name + ","))
                    {
                        if (j > 0)
                        {
                            sb.Append(",");
                        }
                        var aa = dp.GetValue(item);
                        string str = "";
                        if (aa != null)
                        {
                            if (aa.GetType() == typeof(DateTime))
                            {
                                var dd = (DateTime)aa;
                                str = dd.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else str = aa.ToString();
                        }
                        str = str.Replace("\"", "\"\"");//替换英文冒号 英文冒号需要换成两个冒号
                        if (str.Contains(',') || str.Contains('"')
                            || str.Contains('\r') || str.Contains('\n')) //含逗号 冒号 换行符的需要放到引号中
                        {
                            str = string.Format("\"{0}\"", str);
                        }
                        sb.Append(str);
                        j++;
                    }
                }
                sb.Append("\n");
            }
            MemoryStream stream = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(sb.ToString()));
            return stream;
        }
        ///// <summary>
        ///// 用于Web导出
        ///// </summary>
        ///// <param name="dtSource">源DataTable</param>
        ///// <param name="strHeaderText">表头文本</param>
        ///// <param name="strFileName">文件名</param>
        //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string[] columnNames)
        //{
        //    HttpContext curContext = HttpContext.Current;

        //    // 设置编码和附件格式
        //    curContext.Response.ContentType = "application/vnd.ms-excel";
        //    curContext.Response.ContentEncoding = Encoding.UTF8;
        //    curContext.Response.Charset = "";
        //    curContext.Response.AppendHeader("Content-Disposition",
        //        "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

        //    curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, columnNames).GetBuffer());
        //    curContext.Response.End();
        //}

        /// 导出Excel文件
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="entities">数据实体</param>
        /// <param name="dicColumns">列对应关系,如Name->姓名</param>
        /// <param name="title">标题</param>
        /// <returns></returns>
        public static byte[] ExportExcel<T>(List<T> entities, Dictionary<string, string> dicColumns, string title = null)
        {
            if (entities.Count <= 0)
            {
                return null;
            }
            //HSSFWorkbook => xls
            //XSSFWorkbook => xlsx
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("test");//名称自定义
            IRow cellsColumn = null;
            IRow cellsData = null;
            //获取实体属性名
            PropertyInfo[] properties = entities[0].GetType().GetProperties();
            int cellsIndex = 0;
            //标题
            if (!string.IsNullOrEmpty(title))
            {
                ICellStyle style = workbook.CreateCellStyle();
                //边框  
                style.BorderBottom = BorderStyle.Dotted;
                style.BorderLeft = BorderStyle.Hair;
                style.BorderRight = BorderStyle.Hair;
                style.BorderTop = BorderStyle.Dotted;
                //水平对齐  
                style.Alignment = HorizontalAlignment.Left;

                //垂直对齐  
                style.VerticalAlignment = VerticalAlignment.Center;

                //设置字体
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.FontName = "微软雅黑";
                style.SetFont(font);

                IRow cellsTitle = sheet.CreateRow(0);
                cellsTitle.CreateCell(0).SetCellValue(title);
                cellsTitle.RowStyle = style;
                //合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, dicColumns.Count - 1));
                cellsIndex = 2;
            }
            //列名
            cellsColumn = sheet.CreateRow(cellsIndex);
            int index = 0;
            Dictionary<string, int> columns = new Dictionary<string, int>();
            foreach (var item in dicColumns)
            {
                cellsColumn.CreateCell(index).SetCellValue(item.Value);
                columns.Add(item.Value, index);
                index++;
            }
            cellsIndex += 1;
            //数据
            foreach (var item in entities)
            {
                cellsData = sheet.CreateRow(cellsIndex);
                for (int i = 0; i < properties.Length; i++)
                {
                    if (!dicColumns.ContainsKey(properties[i].Name)) continue;
                    //这里可以也根据数据类型做不同的赋值，也可以根据不同的格式参考上面的ICellStyle设置不同的样式
                    object[] entityValues = new object[properties.Length];
                    entityValues[i] = properties[i].GetValue(item);
                    //获取对应列下标
                    index = columns[dicColumns[properties[i].Name]];
                    cellsData.CreateCell(index).SetCellValue(entityValues[i].ToString());
                }
                cellsIndex++;
            }

            byte[] buffer = null;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
                ms.Close();
            }

            return buffer;
        }

        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName, Stream fs = null)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (var file = fs != null ? fs : (new FileStream(strFileName, FileMode.Open, FileAccess.Read)))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="i1"></param>
        /// <returns></returns>
        public static DataTable Import(string strFileName, int i1, int selectrow = 0, Stream fs = null)
        {
            DataTable dt = new DataTable();

            IWorkbook workbook;
            string fileExt = Path.GetExtension(strFileName).ToLower();
            using (var file = fs != null ? fs : (new FileStream(strFileName, FileMode.Open, FileAccess.Read)))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(file); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(file); } else { workbook = null; }
                if (workbook == null) { return null; }

            }
            ISheet sheet = workbook.GetSheetAt(i1);
            IRow headerRow = sheet.GetRow(selectrow);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        public static DataTable Import(string strFileName, string i1, int selectrow = 0, Stream fs = null)
        {
            DataTable dt = new DataTable();

            IWorkbook workbook;
            string fileExt = Path.GetExtension(strFileName).ToLower();
            using (var file = fs != null ? fs : (new FileStream(strFileName, FileMode.Open, FileAccess.Read)))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(file); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(file); } else { workbook = null; }
                if (workbook == null) { return null; }

            }
            ISheet sheet = workbook.GetSheet(i1);
            IRow headerRow = sheet.GetRow(selectrow);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = selectrow+1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row.Cells.Count() >= 36)
                {
                    DataRow dataRow = dt.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }

                    dt.Rows.Add(dataRow);
                }
               
            }
            return dt;
        }


        #region List泛型集合转换为DataTable
        /// <summary>
        /// 将泛型集合类转换成DataTable
        /// </summary>
        /// <typeparam name="T">集合项类型</typeparam>
        /// <param name="list">集合</param>
        /// <returns>数据集(表)</returns>
        public static DataTable ToDataTable<T>(IList<T> list)
        {
            return ToDataTable<T>(list, null);
        }

        /// <summary>
        /// 将泛型集合类转换成DataTable
        /// </summary>
        /// <typeparam name="T">集合项类型</typeparam>
        /// <param name="list">集合</param>
        /// <param name="propertyName">需要返回的列的列名</param>
        /// <returns>数据集(表)</returns>
        public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
                propertyNameList.AddRange(propertyName);
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        var _type = pi.PropertyType;

                        // result.Columns.Add(pi.Name, pi.PropertyType);
                        result.Columns.Add(pi.Name);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            // result.Columns.Add(pi.Name, pi.PropertyType);
                            result.Columns.Add(pi.Name);
                    }
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            if (obj != null)
                            {
                                tempList.Add(obj.ToString().Trim());
                            }
                            else
                            {
                                tempList.Add(obj);
                            }
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                if (obj != null)
                                {
                                    tempList.Add(obj.ToString().Trim());
                                }
                                else
                                {
                                    tempList.Add(obj);
                                }
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

        ///<summary>
        /// Convert a List{T} to a DataTable.
        /// </summary>
        public static DataTable ToDataTable<T>(this List<T> items)
        {
            try
            {
                var tb = new DataTable(typeof(T).Name);

                PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => !x.GetMethod.IsVirtual).ToArray();

                foreach (PropertyInfo prop in props)
                {
                    Type t = GetCoreType(prop.PropertyType);
                    var pName = typeof(T).GetProperty(prop.Name);
                    //4.0或以上版本
                    var displayName = pName.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
                    tb.Columns.Add(displayName, t);
                }

                foreach (T item in items)
                {
                    var values = new object[props.Length];

                    for (int i = 0; i < props.Length; i++)
                    {
                        values[i] = props[i].GetValue(item, null);
                    }

                    tb.Rows.Add(values);
                }
                return tb;
            }
            catch (Exception e)
            {

                throw e;
            }


        }
        /// <summary>
        /// 根据T类型,获取Excel列名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="items"></param>
        /// <returns></returns>
        public static string[] GetExcelClounms<T>(this List<T> items)
        {
            var clounmslist = new List<string>();
            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => !x.GetMethod.IsVirtual).ToArray();

            foreach (PropertyInfo prop in props)
            {
                Type t = GetCoreType(prop.PropertyType);
                var pName = typeof(T).GetProperty(prop.Name);
                //4.0或以上版本
                var displayName = pName.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
                clounmslist.Add(displayName);
            }
            return clounmslist.ToArray();
        }
        /// <summary>
        /// Determine of specified type is nullable
        /// </summary>
        public static bool IsNullable(Type t)
        {
            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
        }

        /// <summary>
        /// Return underlying type if type is Nullable otherwise return the type
        /// </summary>
        public static Type GetCoreType(Type t)
        {
            if (t != null && IsNullable(t))
            {
                if (!t.IsValueType)
                {
                    return t;
                }
                else
                {
                    return Nullable.GetUnderlyingType(t);
                }
            }
            else
            {
                return t;
            }
        }
    }
    #endregion

}